package cn.ainit.edi.app;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.sql.BLOB;
import oracle.sql.TIMESTAMP;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.stereotype.Repository;

import cn.ainit.byf.station.Station;
import cn.ainit.common.DzEDIType;
import cn.ainit.edi.entity.SysDBSchemaCodeList;
import cn.ainit.edi.entity.SysDBSchemaInfo;
import cn.ainit.exchange.entity.ExchangeColumn;
import cn.ainit.exchange.entity.ExchangeGroup;
import cn.ainit.exchange.entity.ExchangeTable;
import cn.ainit.exchange.entity.MappingGroup;
import cn.ainit.exchange.entity.MappingTable;
import cn.ainit.sys.common.dao.Pagination;
import cn.ainit.sys.common.dao.SysBaseDao;
import cn.ainit.sys.constant.SystemConstant;
import cn.ainit.sys.framework.entity.SysColumns;
import cn.ainit.sys.query.datastore.DataStore;
import cn.ainit.sys.query.entity.QueryJson;
import cn.ainit.sys.utils.DateTimeStr;
import cn.ainit.sys.utils.UUID32;

@Repository
public class SysEDIDaoImpl extends SysBaseDao implements SysEDIDao{
	
	@Override
	public boolean executeUSQL(String createSql) throws Exception {
		return this.executeSQL(createSql);
	}
	
	@Override
	public boolean saveSchema(SysDBSchemaInfo schemaInfo) throws Exception {
		try {
			sessionFactory.getCurrentSession().save(schemaInfo);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	@Override
	public boolean saveSchemaFile(InputStream inputStream,String schemaTypeId) throws Exception {
		Connection conn=null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			String testSql = "Select SCHEMA_DATA from sys_db_schema_info where schema_type_id ='"+schemaTypeId+"'";
			System.out.println(this.getJdbcTemplate().queryForList(testSql));
			String strSQL = "Select SCHEMA_DATA from sys_db_schema_info where schema_type_id ='"+schemaTypeId+"' For Update";
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery(strSQL);
			rs.next();
			BLOB blob = (BLOB) rs.getBlob("SCHEMA_DATA");
			OutputStream os = blob.getBinaryOutputStream();// 建立输出流
			BufferedOutputStream output = new BufferedOutputStream(os);
			BufferedInputStream input = new BufferedInputStream(inputStream);
			byte[] buff = new byte[2048000];  //用做文件写入的缓冲
			int bytesRead;
			while(-1 != (bytesRead = input.read(buff, 0, buff.length)))
			{
			     output.write(buff, 0, bytesRead);
			     //System.out.println(bytesRead);
			}
			output.close();
			input.close();
			rs.close();
			conn.commit();
			conn.setAutoCommit(true);
			stmt.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rs!=null){
				rs.close();
			}if(stmt!=null){
				stmt.close();
			}if(conn!=null){
				conn.close();
			}
			
		}
		return false;
	}
	
	/***
	 * 批量保存schemaColumns<SysDBSchemaCodeList>
	 */
	@Override
	public boolean saveSchemaColumns(List<SysDBSchemaCodeList> schemaColumns)
			throws Exception {
		try {
			Session session = this.sessionFactory.openSession();
			Transaction tx = session.beginTransaction();
			for (int i = 0; i < schemaColumns.size(); i++) {
				session.save(schemaColumns.get(i));
				if (i%20==0) {
					session.flush();
					session.clear();
				}
			}
			tx.commit();
			session.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	@Override
	public boolean saveExcGroup(ExchangeGroup group) throws Exception {
		this.getSessionFactory().getCurrentSession().save(group);
		return true;
	}
	
	@Override
	public boolean updateExcGroup(ExchangeGroup group) throws Exception {
		this.getSessionFactory().getCurrentSession().update(group);
		return true;
	}
	
	@Override
	public QueryJson findExcGroup( int pageSize, int pageNumber, String config) throws Exception {
		String sql = "select GROUP_ID,GROUP_NAME,STATION_ID,DATA_SOURCE_TYPE,DBI,USER_NAME,PASSWD,URL,MEMO FROM SYS_EXCHANGE_GROUP";
		QueryJson queryJson = new QueryJson();
		if (pageSize <= 0 ) pageSize = 15;
		if ( pageNumber <= 0) pageNumber = 1;
		Pagination pagination = new Pagination(this.dbType, sql, pageNumber, pageSize, this.getJdbcTemplate());
		queryJson.setRows(pagination.getResultList());
		queryJson.setPageSize(pageSize);
		queryJson.setTotal(pagination.getTotalRows());
		return queryJson;
	}
	
	@Override
	public List<Station> queryStation() throws Exception {
		return this.getSessionFactory().getCurrentSession().createQuery("from Station").list();
	}
	
	@Override
	public List<ExchangeGroup> queryAllExchangeG() throws Exception {
		return this.getSessionFactory().getCurrentSession().createQuery("from ExchangeGroup").list();
	}
	
	@Override
	public List<ExchangeGroup> queryExchangeGById(String groupId)
			throws Exception {
		return this.getSessionFactory().getCurrentSession().createQuery("from ExchangeGroup eg where eg.groupId = '"+groupId+"'").list();
	}
	
	@Override
	public List<DzEDIType> querySourceType() throws Exception {
		List<DzEDIType> dzEDITypes = new ArrayList<DzEDIType>();
		DzEDIType dz1 = new DzEDIType();
		dz1.setCode("1");
		dz1.setTypeCname("XML");
		dz1.setTypeEname("XML");
		DzEDIType dz2 = new DzEDIType();
		dz2.setCode("2");
		dz2.setTypeCname("TXT");
		dz2.setTypeEname("TXT");
		DzEDIType dz3 = new DzEDIType();
		dz3.setCode("3");
		dz3.setTypeCname("数据库");
		dz3.setTypeEname("数据库");
		dzEDITypes.add(dz1);
		dzEDITypes.add(dz2);
		dzEDITypes.add(dz3);
		return dzEDITypes;
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ExchangeTable> queryExcTabByGroupId(String groupId,String root)
			throws Exception {
		if (root.equals("R")) {
			return this.getSessionFactory().getCurrentSession().createQuery("from ExchangeTable et where et.groupId = '"+groupId+"' and (et.superTableId is null or et.superTableId ='root' or et.superTableId = '' or et.superTableId = 'null')").list();
		}
		return this.getSessionFactory().getCurrentSession().createQuery("from ExchangeTable et where et.groupId = '"+groupId+"'").list();
	}
	
	@Override
	public boolean saveExcTable(ExchangeTable exchangeTable) throws Exception {
		this.getSessionFactory().getCurrentSession().save(exchangeTable);
		return true;
	}
	
	@Override
	public boolean updateExcTable(ExchangeTable exchangeTable) throws Exception {
		this.getSessionFactory().getCurrentSession().update(exchangeTable);
		return true;
	}
	
	@Override
	public boolean delExcTable(String groupTableId) throws Exception {
		boolean flag=true;
		Connection conn=null;
		PreparedStatement stmt=null;
		try{
			ExchangeTable exchangeTable = new ExchangeTable();
			exchangeTable.setGroupTableId(groupTableId);
			this.getSessionFactory().getCurrentSession().delete(exchangeTable);
			conn=this.getJdbcTemplate().getDataSource().getConnection();
			String sql="delete from sys_exchange_column where group_table_id = ? ";
			stmt =conn.prepareStatement(sql);
			stmt.setString(1, groupTableId);
		}catch (Exception e) {
			e.printStackTrace();
			flag=false;
		}finally{
			if(stmt!=null){
				stmt.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		
		
		return flag;
	}
	
	@Override
	public List<ExchangeColumn> queryExcClnByIds(String groupId,
			String groupTableId) throws Exception {
		String hql = " from ExchangeColumn ec where 1=1 ";
		String config = "";
		if (groupId != null || !groupId.equals("")) {
			config += " and ec.groupId = '"+groupId+"' ";
		}
		if (groupTableId != null || !groupTableId.equals("")) {
			config += " and ec.groupTableId = '"+groupTableId+"'";
		}
		hql += config;
		return this.getSessionFactory().getCurrentSession().createQuery(hql).list();
	}
	
	@Override
	public ExchangeTable loadExcTableById(String groupTableId) throws Exception {
		return (ExchangeTable) this.getSessionFactory().getCurrentSession().get(ExchangeTable.class, groupTableId);
	}
	
	@Override
	public DataStore loadDataStoreById(String dataSelectDsid) throws Exception {
		return  (DataStore) this.getSessionFactory().getCurrentSession().get(DataStore.class, dataSelectDsid);
	}
	
	@Override
	public List<String> getSqlColumns(String sqlText) throws Exception {
		return this.callBackCol(sqlText);
	}
	
	@Override
	public List<ExchangeColumn> queryExcClnFromDS(String dataSelectDsid)
			throws Exception {
		Query query = this.getSessionFactory().getCurrentSession().createQuery("from SysColumns sc where sc.dsid = '"+dataSelectDsid+"'");
		List<SysColumns> columns = query.list();
		List<ExchangeColumn> exchangeColumns = new ArrayList<ExchangeColumn>();
		for (int i = 0; i < columns.size(); i++) {
			ExchangeColumn exchangeColumn = new ExchangeColumn();
			exchangeColumn.setColId(UUID32.newGuid());
			exchangeColumn.setColName(columns.get(i).getName());
			exchangeColumn.setColCname(columns.get(i).getColnName());
			exchangeColumn.setSortNum((i+1)*10);
			exchangeColumns.add(exchangeColumn);
		}
		return exchangeColumns;
	}
	
	@Override
	public boolean saveExcClns(List<ExchangeColumn> excColumns)
			throws Exception {
		Session session = this.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();
		for (int i = 0; i < excColumns.size(); i++) {
			session.save(excColumns.get(i));
			if ((i+1) % 10 == 0) {
				session.flush();
				session.clear();
			}
		}
		session.flush();
		session.clear();
		tx.commit();
		session.close();
		return true;
	}
	
	@Override
	public boolean delExcClnsByIds(String sqlTxt, String groupId,
			String groupTableId) throws Exception {
		Boolean flag=true;
		Connection conn=null;
		PreparedStatement ps=null;
		try{
			if (groupId == null || groupId.equals("")) {
				throw new RuntimeException("GroupId can't be empty!");
			}
			if (groupTableId == null || groupTableId.equals("")) {
				throw new RuntimeException("groupTableId can't be empty!");
			}
			conn=this.getJdbcTemplate().getDataSource().getConnection();
			ps = conn.prepareStatement(sqlTxt);
			ps.setString(1, groupId);
			ps.setString(2, groupTableId);
			ps.execute();
		}catch (Exception e) {
			e.printStackTrace();
			flag=false;
		}finally{
			if(ps!=null){
				ps.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return true;
	}
	
	@Override
	public QueryJson queryMappingGroup(int pageSize, int pageNumber, String config)
			throws Exception {
		String sql = "select group_mapping_id,group_mapping_name,source_group_id,target_group_id,run_mode,run_time_period,is_valid,last_run_time,run_order,memo from sys_mapping_group";
		QueryJson queryJson = new QueryJson();
		if (pageSize <= 0 ) pageSize = 15;
		if ( pageNumber <= 0) pageNumber = 1;
		Pagination pagination = new Pagination(this.dbType, sql, pageNumber, pageSize, this.getJdbcTemplate());
		queryJson.setRows(pagination.getResultList());
		queryJson.setPageSize(pageSize);
		queryJson.setTotal(pagination.getTotalRows());
		return queryJson;
	}
	
	@Override
	public List queryDzSource(String sqlText) {
		return this.queryDzSourceType(sqlText);
	}
	
	@Override
	public boolean saveMapGroup(MappingGroup mappingGroup) throws Exception {
		this.getSessionFactory().getCurrentSession().save(mappingGroup);
		return true;
	}
	
	@Override
	public boolean updateMapGroup(MappingGroup mappingGroup) throws Exception {
		this.getSessionFactory().getCurrentSession().update(mappingGroup);
		return true;
	}
	
	@Override
	public QueryJson queryMappingTable(int pageSize, int pageNumber, String mappingGroupId)
			throws Exception {
		String sql = "select GROUP_MAPPING_ID,TABLE_MAPPING_ID,TABLE_MAPPING_NAME,SOURCE_TABLE_ID,TARGET_TABLE_ID,TABLE_CONVERT_MODE,TABLE_CONVERT_CLASS,IS_DIVIDE_PAGE,PAGE_ROWS,LAST_EXCHANGE_DATE,INSERT_ROWS,UPDATE_ROWS,DELETE_ROWS,ERROR_ROWS,RUN_ORDER,MEMO,TABLE_MAPPING_TYPE from sys_mapping_table WHERE GROUP_MAPPING_ID = '"+mappingGroupId+"'";
		QueryJson queryJson = new QueryJson();
		if (pageSize <= 0 ) pageSize = 15;
		if ( pageNumber <= 0) pageNumber = 1;
		Pagination pagination = new Pagination(this.dbType, sql, pageNumber, pageSize, this.getJdbcTemplate());
		queryJson.setRows(pagination.getResultList());
		queryJson.setPageSize(pageSize);
		queryJson.setTotal(pagination.getTotalRows());
		return queryJson;
	}
	
	@Override
	public boolean saveMapTable(MappingTable mappingTable) throws Exception {
		this.getSessionFactory().getCurrentSession().save(mappingTable);
		return true;
	}
	
	@Override
	public boolean updateMapTable(MappingTable mappingTable) throws Exception {
		this.getSessionFactory().getCurrentSession().update(mappingTable);
		return true;
	}

	@Override
	public Map<String,List<Map<String, Object>>> xmlIm(String TYPE,String CUSTOMRANGE,String SQLTITLE,String SQL,String SQLITME) {
		String sql ="";
		String sql_title = "";
		String sql_itme="";
		//商品基础信息
		if(SystemConstant.EDI_Amazon_spjcxx_upload.equals(TYPE)){
			 sql_title = SQLTITLE + CUSTOMRANGE; 
			 sql = SQL + CUSTOMRANGE;
		}
		//商品库存信息
		else if(SystemConstant.EDI_Amazon_spkcxx_upload.equals(TYPE)){
			//库存商品SQL
			 sql = SQL + CUSTOMRANGE; 
			//传送标题
			 sql_title = SQLTITLE;
		}
		//亚马逊采购确认单
		else if(SystemConstant.EDI_Amazon_cgqud_upload.equals(TYPE)){
			sql_title = SQLTITLE + CUSTOMRANGE;
			sql = SQL + CUSTOMRANGE;
			sql_itme = SQLITME;
		}
		//亚马逊发货单
		else if(SystemConstant.EDI_Amazon_fhd_upload.equals(TYPE)){
			sql_title = SQLTITLE + CUSTOMRANGE;
			sql = SQL + CUSTOMRANGE;
			sql_itme = SQLITME;
				}
			Map<String,List<Map<String, Object>>> map = new HashMap<String, List<Map<String,Object>>>();
			if(sql.length()>0)
			{
				List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
				map.put("kcsp", list);
			}
			if(sql_title.length()>0)
			{
				List<Map<String, Object>> list_title = this.getJdbcTemplate().queryForList(sql_title);
				map.put("title", list_title);
			}
			if(sql_itme.length()>0)
			{
				List<Map<String, Object>> list_itme = this.getJdbcTemplate().queryForList(sql_itme);
				map.put("kcsp_itme", list_itme);
			}
			 return map;
	}

	@Override
	public void saveLog(String SUPPLY_SITE, String LOGTPYE,
			Boolean COMPLETESTATE, String MSG, String FILENAME,
			String STARTTIME, String ENDTIME, Long FILESIZE,
			String TARGETURL, String LOCALURL, String DATATPYE, String DATAID)  {
		
		String sql="INSERT INTO EDI_LOG VALUES (sys_guid(),'"+SUPPLY_SITE+"','"+LOGTPYE+"','"+(COMPLETESTATE==true?"上传成功":"上传失败")+"','"+MSG+"','"+FILENAME+"',to_date('"+STARTTIME+"','yyyy-mm-dd hh24:mi:ss'),to_date('"+ENDTIME+"','yyyy-mm-dd hh24:mi:ss')," +
				"'"+FILESIZE+"','"+TARGETURL+"','"+LOCALURL+"','"+DATATPYE+"','"+DATAID+"')";
		Connection conn=null;
		Statement stmt = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			stmt = conn.createStatement();
			stmt.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public void saveXml(Map<String, Object> sqlMap) {
		if(sqlMap.get("sql_title")!=null){
			this.getJdbcTemplate().execute(sqlMap.get("sql_title")+"");
		}
		if(sqlMap.get("sql")!=null){
			this.getJdbcTemplate().execute(sqlMap.get("sql")+"");
		}
		List<String> list = (List<String>) sqlMap.get("sql_itme");
		if(list.size()>0){
			for(String s:list)
			{
				 this.getJdbcTemplate().execute(s);
			}
		}
		//调用过程(临时表==>业务表)
//		Connection conn= null;
//		CallableStatement cs = null;
//		try {
//			//关联ID
//			String GLID = sqlMap.get("GLID")+"";
//			//文件类型
//			String fileType = sqlMap.get("fileType")+"";
//			//过程名
//			String STOREDPROCEDURE = sqlMap.get("STOREDPROCEDURE")+"";
//			conn= this.getJdbcTemplate().getDataSource().getConnection();
//			conn.setAutoCommit(false);
//			 String sql = "{Call "+STOREDPROCEDURE+"(?,?)}";
//			 cs = conn.prepareCall(sql);
//			 cs.setString(1,GLID);
//			 cs.setString(2,fileType);
//			 cs.execute();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}finally{
//				try {
//					if(cs != null){
//					cs.close();
//					}
//					if (conn!=null) {
//						conn.close();
//						}
//				} catch (SQLException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
//		}
		
		
	}
}
